package vn.timsua.utils;

import java.util.*;
import java.sql.*;

public class DBPool {

    static LinkedList pool = new LinkedList();
    public static int MAX_CONNECTIONS = 3;
    static int INI_CONNECTIONS = 1;
    private static String driver = "com.mysql.jdbc.Driver";
    private static String user = "root";
    private static String password = "123456";
    private static String url = "jdbc:mysql://localhost:3306/timsua.vn";

    public static Connection getConnection() {
        Connection conn = null;
        synchronized (pool) {
            if (pool.isEmpty()) {
                conn = makeDBConnection();
            } else {
                conn = (Connection) pool.removeFirst();
            }
            try {
                conn.setAutoCommit(true);
            } catch (Exception ex) {
            }
            return conn;
        }
    }

    public static void putConnection(Connection conn) {
        try {
            if (conn == null || conn.isClosed()) {
                System.out.println("DBPool.putConnection: conn is null or closed: " + conn);
                return;
            }

            if (pool.size() >= MAX_CONNECTIONS) {
                conn.close();
                return;
            }
            synchronized (pool) {
                conn.commit();
                conn.setAutoCommit(true);
                pool.addLast(conn);
                pool.notifyAll();
            }
        } catch (SQLException ex) {
        }

    }

    // Remove and close all connections in pool
    public static void release() {
        synchronized (pool) {
            for (Iterator it = pool.iterator(); it.hasNext();) {
                Connection conn = (Connection) it.next();
                try {
                    conn.close();
                } catch (SQLException e) {
                    System.out.println("DBPool.release(): Cannot close connection! (maybe closed?)");
                }
            }
            pool.clear();
        }
    }

    public static int size() {
        synchronized (pool) {
            return pool.size();
        }
    }

    public static boolean isEmpty() {
        synchronized (pool) {
            return pool.isEmpty();
        }
    }

    //--------------------------------------------------------------------------
    private static Connection makeDBConnection() {
        Connection conn = null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("DBPool.makeDBConnection: " + e.getMessage());
        }
        return conn;
    }

    public static void closeStatement(Statement statement) {
        try {
            if (statement != null) {
                statement.close();
            }
        } catch (SQLException e) {
            System.out.println("DBUtils: Cannot close statement.");
            e.printStackTrace();
        }
    }

    public static void main(String args[]) {
        DBPool objPool = new DBPool();
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        try {
            conn = objPool.makeDBConnection();
            pstmt = conn.prepareStatement("SELECT * FROM users");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                System.out.println("ID   : " + rs.getInt("UserId"));
                System.out.println("Name : " + rs.getString("Name"));
                System.out.println("Email: " + rs.getString("Email"));
            }

        } catch (Exception e) {
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
                DBPool.putConnection(conn);
            } catch (Exception e) {
            }
        }

    }
}
